package dao;

import model.*;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.Date;
import java.util.List;
import java.util.Map;

public class UserDao extends BaseDao {
    public JdbcTemplate jdbcTemplate;

    public UserDao() {
        jdbcTemplate = new JdbcTemplate(JDBCUtil.getDataSource());
    }

    public User findByEmail(String email) {
        try {
            return jdbcTemplate.queryForObject("select * from user where email=?", new BeanPropertyRowMapper<>(User.class), email);
        } catch (EmptyResultDataAccessException exception) {
            return null;
        }
    }

    public User findByUsername(String username) {
        try {
            return jdbcTemplate.queryForObject("select * from user where username=?", new BeanPropertyRowMapper<>(User.class), username);
        } catch (EmptyResultDataAccessException exception) {
        }
        return null;
    }

    public List<User> findAll() {
        return jdbcTemplate.query("select * from user", new BeanPropertyRowMapper<>(User.class));
    }

    public List<User> findPage(String realName, Integer startIndex, Integer pageSize) {
        return jdbcTemplate.query("select * from user where real_name like ?  limit ?,? ", new BeanPropertyRowMapper<>(User.class), "%" + realName + "%", startIndex, pageSize);
    }

    public Integer countByRealName(String realName) {
        try {
            return jdbcTemplate.queryForObject("select count(1) from user where real_name like ?", Integer.class, "%" + realName + "%");
        } catch (EmptyResultDataAccessException exception) {
            return null;
        }
    }

    public  List<Map<String, Object>> findDept() {
        return jdbcTemplate.queryForList("SELECT d.name, u.real_name FROM dept  d LEFT join user u on d.id=u.dept_id");
    }

    public int update(String sb,Object[] objects){

        return jdbcTemplate.update(sb,objects);
    }

    public void updateUrl(Long id, String picUrl) {
        jdbcTemplate.update("update user set pic=? where id=?", picUrl, id);
    }

    public List<Meeting> meetingList(String realName,Integer i1,Integer i2) {
        return jdbcTemplate.query("select * from meeting where title like ? limit ?,?",new BeanPropertyRowMapper<>(Meeting.class),"%" + realName + "%",i1,i2);
    }

    public int findCount(String meeting) {
        return jdbcTemplate.queryForObject("select count(1) from meeting where title like ?", Integer.class, "%" + meeting + "%");
    }

    public Meeting meetDetail(Integer i) {
        return jdbcTemplate.queryForObject("SELECT * FROM meeting WHERE id =?",new BeanPropertyRowMapper<>(Meeting.class),i);
    }

    public Integer userDetail(Integer i) {
        return jdbcTemplate.queryForObject("SELECT count(1) FROM meeting_join m LEFT join user u on  m.u_id =u.id where u.id=?",Integer.class,i);
    }

    public List<User> toFocus(Integer i) {
        return jdbcTemplate.query("SELECT r.real_name as realName,r.id FROM userfocus l LEFT join user r on l.user_focus_id=r.id where l.user_id=?",new BeanPropertyRowMapper<>(User.class),i);

    }

    public List<Article> showArticle(String name,Integer i1,Integer i2) {
        return jdbcTemplate.query("SELECT * FROM article where title like ? limit ?,?",new BeanPropertyRowMapper<>(Article.class),"%"+name+"%",i1,i2);
    }

    public List<Article> collectActicle(Integer i) {
        return jdbcTemplate.query("SELECT a.* FROM favorite u LEFT join article a on u.a_id=a.id where u.u_id =?",new BeanPropertyRowMapper<>(Article.class),i);
    }

    public List<Integer> collectCount(Integer id) {
        return jdbcTemplate.query("SELECT count(1) as count from (SELECT * FROM favorite WHERE u_id=?) as tmp LEFT join favorite as f  on tmp.a_id = f.a_id GROUP BY f.a_id",new BeanPropertyRowMapper<>(Integer.class),id);
    }

    public Integer findByArticlename(String s) {
        return jdbcTemplate.queryForObject("SELECT count(1) FROM article where title like ?",Integer.class,"%"+s+"%");
    }

    public int addArticle(String title, String content, int i, Date date, String realName, Integer userId) {
        return jdbcTemplate.update("insert into article(title,content,browse_count,publish_date,publish_real_name,user_id) values (?,?,?,?,?,?)",title,content,i,date,realName,userId);
    }

    public int addMeeting(String s1, Integer i1, String s2, String s3, Date d1, Date d2, Date d3, Integer i2, String s4) {
        return jdbcTemplate.update("insert into meeting(dept_name,dept_id,title,content,publish_date,start_time,end_time,status,make_user) values (?,?,?,?,?,?,?,?,?)",s1,i1,s2,s3,d1,d2,d3,i2,s4);
    }

    public Article articleDetail(Integer id) {
        return jdbcTemplate.queryForObject("select * from article where id= ?",new BeanPropertyRowMapper<>(Article.class),id);
    }

    public int changeFocus(Integer id,Integer i) {
        return jdbcTemplate.update("delete from userfocus where user_id=? and user_focus_id=?",id,i);
    }

    public int addFocus(Integer userId, Integer id) {
        return jdbcTemplate.update("insert into userfocus(user_id,user_focus_id) values (?,?)",userId,id);
    }

    public List<Userfocus> findFocus(Integer userId) {
        return jdbcTemplate.query("SELECT *  FROM userfocus WHERE user_id=?",new BeanPropertyRowMapper<>(Userfocus.class),userId);
    }

//    public User UserDetail(Integer id) {
//        return jdbcTemplate.
//    }
}

